<?php


namespace App\Exports;

use App\Services\BaseService;
use App\Services\CommonService;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithCustomStartCell;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithStyles;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;


/**
 * Class BookCatalogExport29
 * @package App\Exports
 * 29号文导出卷内
 *
 * 整体实现方法：
 * 第一步：  先把全部 卷内数据渲染到 excel ，中间记下需要插入 头部三行的row位置，和各空行插入 点位置
 * 第二步:  插入空行
 * 第三步： 插入头部三行
 *
 */
class BookCatalogLY implements FromCollection, WithStyles, WithCustomStartCell, ShouldQueue, WithEvents
{
    use Exportable;

    const REDIS_EXPIRE_TIME = 600;  //redis有效期

    protected $partCompanyId;     //参见单位
    protected $bookTitleIds;      //用户选择的案卷ids
    protected $column;            //总行数
    protected $headerIndexKey;    //头部三行序号起点
    protected $headerIndexTmp;    //暂存头部三行位置
    protected $redisKey;          //随机字符串
    protected $firstHeaderKey;
    protected $tmp15;
    protected $serialNo;          //案卷序号
    protected $pageFooterKey;     //共几页
    protected $btRow;

    public function __construct($partCompanyId, $projectClassifyId, $bookTitleIds)
    {
        $this->redisKey          = get_rand_str('zj_'); //创造个临时存储redis，用完销毁
        $this->projectClassifyId = $projectClassifyId;
        $this->partCompanyId     = $partCompanyId;
        $this->bookTitleIds      = to_array($bookTitleIds);
        $this->headerIndexKey    = 'ly_head_index:' . $this->redisKey;
        $this->headerIndexTmp    = 'ly_head_index_tmp:' . $this->redisKey;
        $this->firstHeaderKey    = 'ly_first:' . $this->redisKey; //第一个案卷
        $this->tmp15             = 'ly_tmp15:' . $this->redisKey; //用来存一个临时数据，excel每增加15行
        $this->pageFooterKey     = 'ly_page_footer:' . $this->redisKey;
        $this->btRow             = 'ly_btRow:' . $this->redisKey;

    }

    public function collection()
    {
        /**
         * 初始化插入的第一个 头部三行 插入点
         */
        $suffix = $this->partCompanyId . '_' . $this->projectClassifyId;
        $list   = DB::table("book_titles_{$suffix}")->from("book_titles_{$suffix} as bt")
            ->leftJoin("book_catalogs_{$suffix} as bc", 'bt.id', '=', 'bc.book_title_id')
            ->select('bt.id', 'bt.archives_no', 'bc.book_title_id', 'bc.serial_no', 'bc.file_number', 'bc.title', 'bc.page_num', 'bc.remark')
            ->whereIn('bt.id', $this->bookTitleIds)
            ->whereNull('bt.deleted_at')
            ->whereNull('bc.deleted_at')
            ->orderBy('bt.serial_no')
            ->orderBy('bc.serial_no')
            ->get()
            ->groupBy('book_title_id');

        redis()->lpush($this->headerIndexKey, 1);
        redis()->lpush($this->btRow, 4);
        redis()->set($this->headerIndexTmp, 1);

        $resultList = [];
        redis()->set($this->firstHeaderKey, 1); //设置个临时变量，只用于第一个头部写入判断
        $tmpKey = $this->tmp15;
        redis()->set($tmpKey, 0); //设置个临时变量，记录达到15条数据，就插入三行头

        foreach ($list as $val) { //遍历获得每一个案卷的卷内
            $catalogs      = json_decode($val, true);
            $bookTitleId   = $catalogs[0]['book_title_id'];
            $bookTitleName = CommonService::getBookTitleInstance($this->partCompanyId, $this->projectClassifyId)->where('id', $bookTitleId)->value('title');
            $btNameRow = [
                'id'            => 1,
                'btTitle'       => '',
                'archives_no'   => $catalogs[0]['archives_no'],
                'book_title_id' => '',
                'serial_no'     => '',
                'file_number'   => '',
                'title'         => $bookTitleName,
                'page_num'      => '',
                'remark'        => '',
            ]; //每个案卷的卷内数据前都插入一条数据
            array_unshift($catalogs, $btNameRow);

            if (empty($bookTitleId)) continue;  //如果某个案卷为空，略

            $catalogCount = count($catalogs); //卷内数量
            $pageNum      = ceil($catalogCount / 15); //要分成几页（向上取整）
            $remainder    = $catalogCount % 15; //余数
            $emptyRows    = 15 - $remainder; //最后一页卷内需要添加多少空行
            for ($i = 1; $i <= $pageNum; $i++) { //有几页，就把档案号加进去几次
                redis()->incrby($this->headerIndexTmp, 19);
                $headerRows = redis()->get($this->headerIndexTmp);
                redis()->lpush($this->headerIndexKey, $headerRows); //头部三行插入点存进去
                $pageFooter = ['total_page' => $pageNum, 'this_page' => $i];
                $pageFooter = json_encode($pageFooter);
                redis()->lpush($this->pageFooterKey, $pageFooter); //第几页
            }
            $maxSerialNo = max(array_column($catalogs, 'serial_no')); //获取这个案卷里面的最大序号
            foreach ($catalogs as $val2) {
                /**
                 * 增加头部三行
                 */
                if (redis()->get($this->firstHeaderKey) == 1) { //这里只会执行一次
                    $resultList = $this->addHeader($resultList, $val2['archives_no'], $val2);
                    redis()->incrby($this->firstHeaderKey, 1);
                }
                if (redis()->get($tmpKey) == 16) {
                    $resultList = $this->addHeader($resultList, $val2['archives_no'], $val2);
                    redis()->set($tmpKey, 0); //重置为0
                }
                /**
                 * 增加卷内数据
                 */
                $arr['number']      = to_int($val2['serial_no']);
                $arr['file_number'] = format_str($val2['file_number']);
                $arr['title']       = htmlspecialchars_decode($val2['title']);
                $arr['page_num']    = (new BaseService())->formatSerialNo($val2['page_num'], 2);
                $arr['remark']      = '';
                $resultList[]       = $arr;
                if ($val2['title']) redis()->incrby($tmpKey, 1); //发现一条有效数据，加1
                /**
                 * 增加空行
                 */
                if ($maxSerialNo == $val2['serial_no']) { //最后一行
                    if ($remainder) { //有余数
                        $resultList = $this->addEmptyRows($resultList, $emptyRows);
                        redis()->incrby($tmpKey, $emptyRows);
                    }
                }
                /**
                 * 增加页脚
                 */
                if (redis()->get($tmpKey) == 15) {
                    $resultList = $this->addPageFooter($resultList); //增加页脚 第几页共几页
                    redis()->incrby($tmpKey, 1);
                }
            }
        }
        //用完清除
        redis()->del($this->tmp15);
        redis()->del($this->firstHeaderKey);

        redis()->expire($this->btRow, self::REDIS_EXPIRE_TIME);
        redis()->expire($this->headerIndexKey, self::REDIS_EXPIRE_TIME);
        redis()->expire($this->headerIndexTmp, self::REDIS_EXPIRE_TIME);
        redis()->expire($this->pageFooterKey, self::REDIS_EXPIRE_TIME);

        $this->column = count($resultList);

        return collect($resultList);
    }

    /**
     * 添加表头
     * @author: lxk
     * @date: 2023/7/12 17:37
     */
    public function addHeader($resultList, $archivesNo, $val2)
    {
        $header1['number']      = '卷    内    目    录';
        $header1['file_number'] = '';
        $header1['title']       = '';
        $header1['page_num']    = '';
        $header1['remark']      = '';

        $resultList[] = $header1;

        $archivesNo             = format_archives_no_d($archivesNo);
        $header2['number']      = '档案号：' . $archivesNo;
        $header2['file_number'] = '';
        $header2['title']       = '';
        $header2['page_num']    = '';
        $header2['remark']      = '';

        $resultList[] = $header2;

        $header3['number']      = '顺
号';
        $header3['file_number'] = '图 号 或
文件编号';
        $header3['title']       = '名             称';
        $header3['page_num']    = '张次';
        $header3['remark']      = '备注';

        $resultList[] = $header3;

        if ($val2['title'] && format_str(empty($val2['page_num']))) {
            $headerRows = redis()->get($this->headerIndexTmp); //获取当前的头插入行
            $headerRows = $headerRows + 3;
            redis()->lpush($this->btRow, $headerRows);
        }

        return $resultList;
    }

    /**
     * 增加几个空行
     */
    public function addEmptyRows($resultList, $num)
    {
        for ($i = 0; $i < $num; $i++) {
            $empty['number']      = ' '; //这里必须给个空格，如果为空字符串的话，有时候会产生bug！
            $empty['file_number'] = ' ';
            $empty['title']       = ' ';
            $empty['page_num']    = ' ';
            $empty['remark']      = ' ';

            $resultList[] = $empty;
        }
        return $resultList;
    }

    /**
     * 增加页脚
     *
     * @param $resultList
     * @return array
     * @author: lxk
     * @date: 2024/6/3 9:18
     */
    public function addPageFooter($resultList): array
    {
        $pageFooter['number']      = ' ';
        $pageFooter['file_number'] = ' ';
        $pageFooter['title']       = ' ';
        $pageFooter['page_num']    = ' ';
        $pageFooter['remark']      = ' ';

        $resultList[] = $pageFooter;

        return $resultList;
    }

    /**
     * 样式设置
     */
    public function styles(Worksheet $sheet)
    {
        $headerCount = redis()->llen($this->headerIndexKey);
        for ($i = 0; $i < $headerCount - 1; $i++) {
            $headerStartIndex = redis()->rpop($this->headerIndexKey);
            $pageFooter = redis()->rpop($this->pageFooterKey);
            $nineteen = $headerStartIndex + 18; //页脚
            $pageFooter = json_decode($pageFooter, true);
            $sheet->setCellValue('A' . $nineteen, "共{$pageFooter['total_page']}页，第{$pageFooter['this_page']}页 ");
        }
    }

    /**
     * 文件编号有  20211933115109 这样的，为了避免变成科学计数法
     *
     * @return \Closure[]
     * @author: lxk
     * @date: 2025-09-01 14:18
     */
    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function (AfterSheet $event) {
                $sheet   = $event->sheet->getDelegate();
                $lastRow = $sheet->getHighestRow();
                if ($lastRow < 3) {
                    return; // 没有数据行,也就是防止空案卷报错
                }
                // 文件编号有  20211933115109 这样的，为了避免变成科学计数法
                $range = 'B4:B' . $lastRow;
                $sheet->getStyle($range)
                    ->getNumberFormat()
                    ->setFormatCode('@');
                // 确保所有值都是字符串格式
                foreach ($sheet->getColumnIterator('B') as $column) {
                    if ($column->getColumnIndex() == 'B') {
                        foreach ($column->getCellIterator() as $cell) {
                            $cellVal = $cell->getValue();
                            if (is_numeric($cellVal)) {
                                $cell->setValueExplicit((string)$cellVal);
                            }
                        }
                    }
                }
            },
        ];
    }

    /**
     * 从哪个单元格开始渲染数据，数据集合如 $list
     * @return string
     */
    public function startCell(): string
    {
        return 'A1';
    }

}
